package rubix.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class UserManager {

    private static final String TBLNAME = "user";

    private static void handleSQLException(SQLException ex, String sql, String... parameters) {
        String msg = "Unable to access data; SQL=" + sql + "\n";

        for (String parameter : parameters) {
            msg += "," + parameter;
        }

        Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, msg, ex);
        throw new RuntimeException(msg, ex);
    }
    //method
    public static void prepareTables(){
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";
        
        try {
            conn = ConnectionManager.getConnection();            
            
            sql = "DROP TABLE IF EXISTS user";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            
            sql = "CREATE TABLE IF NOT EXISTS user("
                    + "userid VARCHAR(15) NOT NULL,"
                    + "password VARCHAR(25) NOT NULL,"
                    + "name VARCHAR(20) NOT NULL,"
                    + "department VARCHAR(20) NOT NULL,"                    
                    + "PRIMARY KEY (userid)"
                    + ")";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "prepareTables");
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }    
    
    public static void addUser(User user) {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";
        
        String username = user.getUsername();
        String password = user.getPassword();
        String name = user.getName();
        String department = user.getDepartment();
        
        try {
            conn = ConnectionManager.getConnection();
            
            sql = "INSERT INTO " + TBLNAME + " (userid,password,name,department) VALUES (?,?,?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, username);
            stmt.setString(2, password);
            stmt.setString(3, name);
            stmt.setString(4, department);

            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "username=" + username + ",password=" + password + ",name=" + name + ",department=" + department);
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }
    
    public static ArrayList<User> retrieveAll() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        ArrayList<User> results = new ArrayList<User>();

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT userid,password,name,department FROM " + TBLNAME;
            stmt = conn.prepareStatement(sql);

            rs = stmt.executeQuery();
            while (rs.next()) {
                String username = rs.getString("userid");
                String password = rs.getString("password");
                String name = rs.getString("name");
                String department = rs.getString("department");

                results.add(new User(username, password, name, department));
            }
        } catch (SQLException ex) {
            handleSQLException(ex, sql);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return results;
    }

    public static User retrieveUser(String username) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        User result = null;

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT password,name,department FROM " + TBLNAME + " WHERE userid = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, username);

            rs = stmt.executeQuery();

            while (rs.next()) {
                String password = rs.getString("password");
                String name = rs.getString("name");
                String department = rs.getString("department");

                result = new User(username, password, name, department);
            }
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "username=" + username);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return result;
    }
    
    public static ArrayList<String> retrieveDepartment() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        ArrayList<String> results = new ArrayList<String>();

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT department FROM " + TBLNAME;
            stmt = conn.prepareStatement(sql);

            rs = stmt.executeQuery();
            while (rs.next()) {
                String department = rs.getString("department");

                results.add(department);
                
            }
        } catch (SQLException ex) {
            handleSQLException(ex, sql);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return results;
    }
    
        public static ArrayList<String> retrieveUniqueDepartment() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        ArrayList<String> results = new ArrayList<String>();

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT department FROM " + TBLNAME;
            stmt = conn.prepareStatement(sql);

            rs = stmt.executeQuery();
            while (rs.next()) {
                String department = rs.getString("department");
             
                results.add(department);
            }
            TreeSet set = new TreeSet(results);
            results.clear();
            results = new ArrayList<String>(set);
        } catch (SQLException ex) {
            handleSQLException(ex, sql);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return results;
    }
    
    public static void updateDepartment(String username, String department) {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";

        try {
            conn = ConnectionManager.getConnection();

            sql = "UPDATE " + TBLNAME + " set department=? where userid = ?";
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, department);
            stmt.setString(2, username);

            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "username=" + username + ",department=" + department);
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }
}